package hr.dao;

import java.util.ArrayList;
import java.util.Date;

import com.sun.xml.internal.ws.org.objectweb.asm.Type;

import resources.bean.Login;
import resources.dao.DatabaseDAO;
import resources.pojo.hr_branch;
import resources.pojo.hr_department;
import resources.pojo.hr_designation;
import resources.pojo.hr_employee;
import resources.util.Tool;

public class EmployeeDAO extends DatabaseDAO{

	public static boolean action(String action,hr_employee object){
		int row = 0;
		try{
			getDBPoolConnection();
			conn.setAutoCommit(false);
			String query = "call sp_hr_employee(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			cs =  conn.prepareCall(query);
			
			cs.setString(1,action);
			//insert into employee
			cs.setString(2, object.getId());cs.setString(3, object.getNumber());
			cs.setString(4, object.getFirstname());cs.setString(5, object.getLastname());
			cs.setInt(6, object.getGender());cs.setString(7, Tool.format2.format(object.getBirthday()));
			cs.setInt(8, object.getType());cs.setInt(9, object.getStatus());cs.setString(10, object.getBranch().getId());
			cs.setString(11, object.getDepartment().getId());cs.setString(12,object.getDesignation().getId());
			cs.setString(13, object.getEmail_of_company());cs.setString(14, object.getBank());
			cs.setString(15, object.getAccount_number());cs.setString(16, object.getImage());
			cs.setString(17, Login.user.getUsername());cs.setString(18, Tool.format2.format(new Date()));
			cs.setString(19,  Login.user.getUsername());cs.setString(20, Tool.format2.format(new Date()));
			
			//insert into employee detail
			cs.setString(21, object.getCellphone());
			cs.setString(22, object.getEmail());
			cs.setString(23, object.getAddress_permanent());
			cs.setString(24, object.getAddress_current());
			cs.setString(25, object.getIdentity_card());
			cs.setString(26, object.getDate_of_issue()==null?Tool.format2.format(new Date()):Tool.format2.format(object.getDate_of_issue()));
			cs.setString(27, object.getPlace_of_issue());
			cs.setInt(28, object.getMarital());
			cs.setString(29, object.getFamily());
			row = cs.executeUpdate();
			conn.commit();
		}catch (Exception e) {
			row = 0;
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			closeConnection();
		}
		return row > 0;
	}
	
	public static ArrayList<hr_employee> getListEmployee(String name,int page, int row){
		ArrayList<hr_employee> temp = null;
		try{
			getDBPoolConnection();
			conn.setAutoCommit(false);
			String query = "call sp_hr_employee_paging(?,?,?,?)";
			
			cs =  conn.prepareCall(query);
			cs.setString(1,"%"+name+"%");
			cs.setInt(2,page);
			cs.setInt(3,row);
			cs.registerOutParameter(4, Type.INT);
			rs = cs.executeQuery();
			hr_employee object = null;
			temp = new ArrayList<hr_employee>();
			while(rs.next()){
				object = new hr_employee();
				object.setId(rs.getString("id"));
				object.setNumber(rs.getString("number"));
				object.setFirstname(rs.getString("firstname"));
				object.setLastname(rs.getString("lastname"));
				object.setGender(rs.getInt("gender"));
				object.setBirthday(rs.getDate("birthday"));
				object.setType(rs.getInt("type"));
				object.setStatus(rs.getInt("status"));
				object.setBranch(new hr_branch());
				object.getBranch().setId(rs.getString("branch"));
				object.setDepartment(new hr_department());
				object.getDepartment().setId(rs.getString("department"));
				object.getDepartment().setName(rs.getString("d.name"));
				object.setDesignation(new hr_designation());
				object.getDesignation().setId(rs.getString("designation"));
				object.getDesignation().setName(rs.getString("de.name"));
				object.setEmail_of_company(rs.getString("email_of_company"));
				object.setBank(rs.getString("bank"));
				object.setAccount_number(rs.getString("account_number"));
				object.setImage(rs.getString("image"));
				object.setCellphone(rs.getString("cellphone"));
				object.setEmail(rs.getString("email"));
				object.setAddress_permanent(rs.getString("address_permanent"));
				object.setAddress_current(rs.getString("address_current"));
				object.setIdentity_card(rs.getString("identity_card"));
				object.setDate_of_issue(rs.getDate("date_of_issue"));
				object.setPlace_of_issue(rs.getString("place_of_issue"));
				object.setMarital(rs.getInt("marital"));
				object.setFamily(rs.getString("family"));
				object.setCreated_by(rs.getString("created_by"));
				object.setCreated_date(rs.getDate("created_date"));
				object.setModified_by(rs.getString("modified_by"));
				object.setModified_date(rs.getDate("modified_date"));
				temp.add(object);
			}
			if(temp.size() > 0){
				Tool.page = cs.getInt(4);
			}
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}finally{
			closeConnection();
		}
		return temp;
	}
	
	public static ArrayList<hr_employee> getListEmployee(String action,String name){
		ArrayList<hr_employee> temp = null;
		try{
			getDBPoolConnection();
			conn.setAutoCommit(false);
			String query = "call sp_hr_employee_select(?,?)";
			
			cs =  conn.prepareCall(query);
			cs.setString(1,action);
			cs.setString(2,"%"+name+"%");
			cs.registerOutParameter(4, Type.INT);
			rs = cs.executeQuery();
			hr_employee object = null;
			temp = new ArrayList<hr_employee>();
			while(rs.next()){
				object = new hr_employee();
				object.setId(rs.getString("id"));
				object.setNumber(rs.getString("number"));
				object.setFirstname(rs.getString("firstname"));
				object.setLastname(rs.getString("lastname"));
				object.setGender(rs.getInt("gender"));
				object.setBirthday(rs.getDate("birthday"));
				object.setType(rs.getInt("type"));
				object.setStatus(rs.getInt("status"));
				object.setBranch(new hr_branch());
				object.getBranch().setId(rs.getString("branch"));
				object.setDepartment(new hr_department());
				object.getDepartment().setId(rs.getString("department"));
				object.getDepartment().setName(rs.getString("d.name"));
				object.setDesignation(new hr_designation());
				object.getDesignation().setId(rs.getString("designation"));
				object.getDesignation().setName(rs.getString("de.name"));
				object.setEmail_of_company(rs.getString("email_of_company"));
				object.setBank(rs.getString("bank"));
				object.setAccount_number(rs.getString("account_number"));
				object.setImage(rs.getString("image"));
				object.setCreated_by(rs.getString("created_by"));
				object.setCreated_date(rs.getDate("created_date"));
				object.setModified_by(rs.getString("modified_by"));
				object.setModified_date(rs.getDate("modified_date"));
				temp.add(object);
			}
			if(temp.size() > 0){
				Tool.page = cs.getInt(4);
			}
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}finally{
			closeConnection();
		}
		return temp;
	}
}
